<?php

use Carbon\Carbon;

class ReportController extends \BaseController {

    /**
     * Display a listing of the resource.
     *
     * @return Response
     */
    public function index() {
        $today = Carbon::now();
        $date = date('Y-m-d');

        $check_out = DB::select('select r.*,c.* '
                        . 'from rentals as r '
                        . 'left join cars as c '
                        . 'on r.vehicle = c.id '
                        . 'where r.status ="CHECK_OUT" AND '
                        . '("' . $today . '" >= r.STARTTIME AND "' . $today . '" < r.ENDTIME) ');

        $checkOut_count = DB::select('SELECT COUNT(DISTINCT r2.vehicle) AS count_vehicles '
                        . 'from rentals as r2 '
                        . 'where r2.status ="CHECK_OUT" AND '
                        . '("' . $today . '" >= r2.STARTTIME AND "' . $today . '" < r2.ENDTIME) '
        );

        $rezerved = DB::select('select r.*,c.* from rentals as r '
                        . 'left join cars as c '
                        . 'on c.id=r.vehicle '
                        . 'where (r.status="REZERVED" AND '
                        . '("' . $today . '" >= r.STARTTIME AND "' . $today . '" < r.ENDTIME ))');

        $rezerved_count = DB::select('SELECT COUNT(DISTINCT r2.vehicle) AS count_vehicles '
                        . 'from rentals as r2 '
                        . 'where (r2.status ="REZERVED" AND '
                        . '("' . $today . '" >= r2.STARTTIME AND "' . $today . '" < r2.ENDTIME ))');


        $checkIn_count = DB::select('SELECT COUNT(DISTINCT r.vehicle) AS count_vehicles '
                        . 'from rentals as r '
                        . 'WHERE r.vehicle '
                        . 'NOT IN (SELECT r3.vehicle from rentals as r3 '
                        . 'where (r3.status = "CHECK_OUT" AND ("' . $today . '" >= r3.STARTTIME '
                        . 'AND "' . $today . '" < r3.ENDTIME)) '
                        . 'OR (r3.status="REZERVED" AND ("' . $today . '" >= r3.STARTTIME '
                        . 'AND "' . $today . '" < r3.ENDTIME))) ');

        $delayed_count = DB::select('SELECT COUNT(DISTINCT r2.vehicle) AS count_vehicles '
                        . 'from rentals as r2 '
                        . 'where r2.status ="CHECK_OUT" '
                        . 'AND "' . $today . '" >= r2.ENDTIME');

        $online = DB::select('select o.*, c.*, ob.* '
                        . 'from online_reservation as o '
                        . 'left join cars as c '
                        . 'on o.vehicleID = c.id '
                        . 'left join online_booking as ob '
                        . 'on o.bookID = ob.bookID '
                        . 'where o.statusID ="4" '
                        . 'AND ("' . $today . '" >= ob.startDate AND "' . $today . '" < ob.endDate) ');

        $online_count = DB::select('SELECT COUNT(DISTINCT o2.vehicleID) AS count_vehicles '
                        . 'from online_reservation as o2 '
                        . 'left join online_booking as ob2 '
                        . 'on o2.bookID = ob2.bookID '
                        . 'where o2.statusID="4" '
                        . 'AND ("' . $today . '" >= ob2.startDate AND "' . $today . '" < ob2.endDate) ');

        $todayGiven = DB::select('select r.*,c.* , COUNT(DISTINCT r.vehicle) '
                        . 'as count_vehicles '
                        . 'from rentals as r '
                        . 'left join cars as c '
                        . 'on r.vehicle =c.id '
                        . 'where DATE(STARTTIME) = "' . $date . '"');

        $todayBack = DB::select('select r.*,c.*, COUNT(DISTINCT r.vehicle) '
                        . 'as count_vehicles '
                        . 'from rentals as r '
                        . 'left join cars as c '
                        . 'on r.vehicle =c.id '
                        . 'where DATE(ENDTIME) = "' . $date . '"');

        $qiraAfatgjate_count = DB::select('select r.*, c.*, COUNT(DISTINCT r.vehicle) '
                        . 'as count_vehicles '
                        . 'from rentals as r '
                        . 'left join cars as c '
                        . 'on r.vehicle = c.id '
                        . 'where r.rentdays > 31 ');

        $qiraAfatshkurter_count = DB::select('select r.*, c.*, COUNT(DISTINCT r.vehicle) as count_vehicles '
                        . 'from rentals as r '
                        . 'left join cars as c '
                        . 'on r.vehicle = c.id '
                        . 'where r.rentdays <= 31 ');

        $servis = DB::select('select c.*, i.* '
                        . 'from insur_docs as i '
                        . 'left join cars as c '
                        . 'on c.id=i.car_id '
                        . 'where (i.sgs < "' . $today . '" '
                        . 'OR i.tpl < "' . $today . '" '
                        . 'OR i.sgs < "' . $today . '" '
                        . 'OR i.inter_permis < "' . $today . '") ');

        $servis_count = DB::select('SELECT COUNT(DISTINCT c.id ) AS count_vehicles '
                        . 'from insur_docs as i '
                        . 'left join cars as c '
                        . 'on c.id=i.car_id '
                        . 'where (i.sgs < "' . $today . '" '
                        . 'OR i.tpl < "' . $today . '" '
                        . 'OR i.sgs < "' . $today . '" '
                        . 'OR i.inter_permis < "' . $today . '")');

        return View::make('pages.reports', array(
                    'checkOut_count' => $checkOut_count[0]->count_vehicles,
                    'checkIn_count' => $checkIn_count[0]->count_vehicles,
                    'rezerved_count' => $rezerved_count[0]->count_vehicles,
                    'delayed_count' => $delayed_count[0]->count_vehicles,
                    'todayGiven_count' => $todayGiven[0]->count_vehicles,
                    'todayBack_count' => $todayBack[0]->count_vehicles,
                    'qiraAfatgjate_count' => $qiraAfatgjate_count[0]->count_vehicles,
                    'qiraAfatshkurter_count' => $qiraAfatshkurter_count[0]->count_vehicles,
                    'online_count' => $online_count[0]->count_vehicles,
                    'servis_count' => $servis_count[0]->count_vehicles,
                    'check_out' => $check_out,
                    'online' => $online,
                    'servis' => $servis,
                    'today' => $today,
                    'today_given' => $todayGiven,
                    'today_back' => $todayBack,
        ));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return Response
     */
    public function create() {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @return Response
     */
    public function store() {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return Response
     */
    public function show($id) {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return Response
     */
    public function edit($id) {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  int  $id
     * @return Response
     */
    public function update($id) {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return Response
     */
    public function destroy($id) {
        //
    }

}
